package com.opentutor.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.opentutor.database.Database;
import com.opentutor.model.Chapter;

public class ChapterDaoImpl implements ChapterDao {

	@Override
	public List<Chapter> getAllChapters() {

		List<Chapter> result = fetchChaptersFromDatabase("select * from chapter");
		return result;
	}

	@Override
	public List<Chapter> getAllChaptersOfACourse(int courseId) {

		String query = "select * from chapter where course_id = " + courseId;
		List<Chapter> result = fetchChaptersFromDatabase(query);
		return result;
	}

	@Override
	public Chapter getChapterById(int id) {
		String query = "select * from chapter where id = " + id;
		List<Chapter> result = fetchChaptersFromDatabase(query);

		if (result.size() == 1)
			return result.get(0);
		else
			System.out.println("NO Record exists with id:  " + id);

		return null;
	}

	@Override
	public void addChapter(Chapter chapter) {

		try {
			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("insert into chapter(name, description, course_id) values(?,?,?)");

			stmt.setString(1, chapter.getName());
			stmt.setString(2, chapter.getDescription());
			stmt.setInt(3, chapter.getCourseId());

			stmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	@Override
	public void upateChapter(Chapter chapter) {
		try {
			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("update chapter set name=?,  description=?, course_id=?, updated_date = now() where id=?");

			stmt.setString(1, chapter.getName());
			stmt.setString(2, chapter.getDescription());
			stmt.setInt(3, chapter.getCourseId());
			stmt.setInt(4, chapter.getId());

			stmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	@Override
	public void deleteChapter(Chapter chapter) {
		try {
			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("delete from chapter where id=?");

			stmt.setInt(1, chapter.getId());

			stmt.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	private List<Chapter> fetchChaptersFromDatabase(String query) {
		try {
			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn.prepareStatement(query);
			ResultSet result = stmt.executeQuery();

			List<Chapter> results = getAllChapterFromResultSet(result);

			return results;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	private List<Chapter> getAllChapterFromResultSet(ResultSet result)
			throws SQLException {
		List<Chapter> chapters = new ArrayList<Chapter>();
		while (result.next()) {
			Chapter chapter = getChapter(result);
			chapters.add(chapter);
		}
		return chapters;
	}

	private Chapter getChapter(ResultSet result) throws SQLException {
		Chapter chapter = new Chapter();

		chapter.setId(result.getInt("id"));
		chapter.setName(result.getString("name"));
		chapter.setDescription(result.getString("description"));
		chapter.setCourseId(result.getInt("course_id"));

		return chapter;
	}
}
